package mysql;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import crawler_twitter.CrawlerUtentiByItem;


import winterwell.jtwitter.Twitter.User;


/*classe responsabile di salvare i dati nel db
 * 
 */
public class Tweet2db {
	
	private Connection connection;
	private static Tweet2db t2d;

	
	public void close(){
		Connector.getInstance().disconnetti();
	}
	

	public static Tweet2db getInstance() {
		if (t2d == null)
			t2d = new Tweet2db();
		return t2d;
	}
	
		public int nuovoUtente(String screenName) {
		int id = 0;
		int rt = 0;
		connection = Connector.getInstance().connetti();

		try {
			
			//verifico se l'utente ? presente nel db
			ResultSet rs;
			String query = "SELECT id FROM utenti WHERE (screen_name = ?)";
			PreparedStatement stmt = connection.prepareStatement(query);
			stmt.setString(1, screenName);
			rs = stmt.executeQuery();
			if (rs.next()){
				id = rs.getInt(1);
				rs.close();
				stmt.close();
				return id;
			}
			
			
			//l'utente non ? presente nel db allora lo inserisco
			query = "INSERT INTO utenti (screen_name) VALUES (?)";
		    stmt = connection.prepareStatement(query);
			stmt.setString(1, screenName);
			rt = stmt.executeUpdate();
			stmt.close();
			if (rt == 1){
				//ricavo il codice dell'utente
				query = "SELECT id FROM utenti WHERE (screen_name = ?)";
				stmt = connection.prepareStatement(query);
				stmt.setString(1, screenName);
				rs = stmt.executeQuery();
				if (rs.next())
					id = rs.getInt(1);
			}
			rs.close();
			stmt.close();
			
		} catch (Exception e) {
			e.printStackTrace();
			String errore = e.getMessage();
			System.out.println(errore);
		}finally{
			close();
		}
		return id;

		
	}

	//inserisce il tweet nel db e ne restituisce il codice
	public int nuovoTweet(String tweet, long data, int codUser, String item) {
		connection = Connector.getInstance().connetti();
		int id = 0;

		String query = "INSERT INTO  tweet (msg, id_utente, data, item_searched) VALUES (?,?,?,?)";
		PreparedStatement stmt;
		ResultSet rs;
		try {
			stmt = connection.prepareStatement(query);
			stmt.setString(1, tweet);
			stmt.setInt(2, codUser);
			stmt.setDate(3, new Date(data));
			stmt.setString(4,item);

			int rt = stmt.executeUpdate();
			stmt.close();
			if (rt == 1){
				//ricavo il codice dell'utente
				query = "SELECT id FROM tweet WHERE (msg = ?) AND (id_utente = ?)";
				stmt = connection.prepareStatement(query);
				stmt.setString(1, tweet);
				stmt.setInt(2, codUser);

				rs = stmt.executeQuery();
				if (rs.next())
					id = rs.getInt(1);
				rs.close();
				stmt.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
			String errore = e.getMessage();
			System.out.println(errore);
		}finally{
			close();
		}
		
		return id;
	}
	
	//Inserisce la valutazione dei tweet
	public void insertTweetValutation(int id,String valutation) {
		connection = Connector.getInstance().connetti();

		String query = "UPDATE tweet SET item_searched_valutation = ? WHERE (id = ?)";
		PreparedStatement stmt;
		try {
			stmt = connection.prepareStatement(query);
			stmt.setString(1, valutation);
			stmt.setInt(2,id);
			stmt.execute();
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
			String errore = e.getMessage();
			System.out.println(errore);
		}finally{
			close();
		}
	}

	

	//inserisce le amicizie nel db senza duplicati e con data di inserimento
	public void inserisciAmici(String screenName, List<User> amici) {
		String query;
		PreparedStatement stmt;
		ResultSet rs;
		int id;
		
		connection = Connector.getInstance().connetti();
		try{
			//ricavo l'id dell'utente attuale (user)
			query = "SELECT id FROM utenti WHERE (screen_name = ?)";
			stmt = connection.prepareStatement(query);
			stmt.setString(1, screenName);
			rs = stmt.executeQuery();

			if(rs.next()){
				id = rs.getInt(1);

				for(User u : amici){
					//verifico se l'amico ? gia stato inserito
					query = "SELECT id FROM amici WHERE (username = ? AND cod_utente = ?)";
					stmt = connection.prepareStatement(query);
					stmt.setString(1, u.getScreenName());
					stmt.setInt(2, id);
					rs = stmt.executeQuery();

					//se non ? presente nel db inserisco l'amicizia
					if (!rs.next()){
						query = "INSERT INTO  amici (username, cod_utente, dt_rilevamento_amicizia) VALUES (?,?,?)";
						stmt = connection.prepareStatement(query);
						stmt.setString(1, u.getScreenName());
						stmt.setInt(2, id);
						stmt.setDate(3, new Date(System.currentTimeMillis()));
						stmt.executeUpdate();
					}
				}
			}	
			
			rs.close();
			stmt.close();
			Connector.getInstance().disconnetti();

		}catch(Exception e){
			e.printStackTrace();
		}
	}

}
